with  
--客户标签	
dwd_tab_ops_intervene_input_dt as (
 select 
     t.waybill_no     --运单号
    ,max(t.business_type)  business_type --客户标签	
 from jms_dwd.dwd_tab_ops_intervene_input_dt t
 where t.dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
            and business_type in ('labelShsm','labelMustCall') 
            and waybill_no is not null
 group by t.waybill_no
)
--通话记录表
,call_record_tmp as ( 
     select 
           waybill_no       -- 运单编号
          ,min(concat(start_time,'@',nvl(end_time,'0000-00-00'),'@',connect_status,'@',duration_seconds)) as call_time
     from jms_dwd.dwd_sqs_call_record_base_dt 
     where dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
              and waybill_no is not null
    group by  waybill_no
)
,dwd_wide_sign_summary_waybill_dt as ( 
    select 
     max(t.end_network_arrival_early_time)  end_network_arrival_early_time  --末端网点最早到件时间
    ,max(t.sign_later_scantime           )  sign_later_scantime             --最后一次签收时间
    ,max(t.outstore_later_scantime       )  outstore_later_scantime         --最后一次出库时间
    ,max(t.instore_later_scantime        )  instore_later_scantime          --最后一次入库时间
    ,t.waybill_no                        --单号
     from jms_dwd.dwd_wide_sign_summary_waybill_dt t 
     where  t.dt between date_add('{{ execution_date | cst_ds }}', -30) and '{{ execution_date | cst_ds }}'
    group by t.waybill_no
)

,dwd_yl_oms_oms_order_incre_dt as (
    select
     max(concat(substr(t.receiver_mobile_phone,1,3) ,substr(t.receiver_mobile_phone,8,4)))  receiver_mobile_phone  --前四后三手机号
    ,t.waybill_id as  waybill_no                        --单号
     from jms_dwd.dwd_yl_oms_oms_order_incre_dt t
     where  t.dt between date_add('{{ execution_date | cst_ds }}', -7) and '{{ execution_date | cst_ds }}'
    group by t.waybill_id
)

,dm_reverse_marking_performance_detail_dt_tmp as (
select t.dt as date_time                                                                        --日期
     , t3.end_network_arrival_early_time  as      arrival_time                                  --到件时间
     , t.waybill_no                                                                             --运单号
     , t1.business_type                                                                         --客户标签
     , t.is_door                                                                                --是否送货上门,1是0否
     , if(split(t2.call_time,'@')[2] is null or split(t2.call_time,'@')[2] ='' or split(t2.call_time,'@')[2]='0',0,1) as  is_performance_flag                                                                --是否派前电联,1是0否

     --, case when greatest(nvl(t3.sign_later_scantime,'0000-00-00'),nvl(t3.outstore_later_scantime,'0000-00-00') ) > split(t2.call_time,'@')[0] then 1
     --       when if(split(t2.call_time,'@')[2] is null or split(t2.call_time,'@')[2] ='' or split(t2.call_time,'@')[2]='0',0,1)=1 then 1
     --       else 0 end  is_performance_flag


     , split(t2.call_time,'@')[0] as  call_start_time                                           --通话开始时间
     , split(t2.call_time,'@')[1] as  call_end_time                                             --通话结束时间
     , split(t2.call_time,'@')[2] as  call_connect_status                                       --通话状态
     , split(t2.call_time,'@')[3] as  call_duration_seconds                                     --实际通话时长
     , t3.instore_later_scantime as instore_time                                                --入库时间
     , t.business_name                                                                          --投递类型
     , greatest(nvl(t3.sign_later_scantime,'0000-00-00'),nvl(t3.outstore_later_scantime,'0000-00-00') ) as  sign_time             --签收出库时间
     , t.signed_type                                                                            --签收类型
     , t.scan_user_code                                                                         --派件员code
     , t.scan_user                                                                              --派件员name
     , t.scan_site_code                                                                         --扫描网点code
     , t.scan_site_name                                                                         --扫描网点name
     , t.fran_code                                                                              --加盟商code
     , t.fran_name                                                                              --加盟商
     , t.agent_code                                                                             --代理区code
     , t.agent_name                                                                             --代理区
     , t.ordersource_code                                                                       --订单来源coed
     , t.ordersource_name                                                                       --订单来源
     , t.is_reback                                                                              --是否退件
     , case when t.source ='共配' then 1 else 0 end as  gp_flg                                  --共配标签,1是0否
     , case when t.source ='PC端' then 1 else 0 end as  pc_flg                                  --PC端标签,1是0否
     , t4.receiver_mobile_phone
     , count(t.waybill_no)over(partition by t4.receiver_mobile_phone,t.scan_site_code,t.dt ) as count_num   --单量
     , row_number()over(partition by t4.receiver_mobile_phone,t.scan_site_code,t.dt order by  nvl(split(t2.call_time,'@')[0],'9999-99-99' ) asc ) as rn --排序
     , t.dt
from jms_dm.dm_door_sign_rate_detail_dt t
join dwd_tab_ops_intervene_input_dt t1 on t.waybill_no=t1.waybill_no
left join call_record_tmp t2 on t.waybill_no=t2.waybill_no
left join dwd_wide_sign_summary_waybill_dt t3 on t.waybill_no=t3.waybill_no
left join dwd_yl_oms_oms_order_incre_dt t4 on t.waybill_no=t4.waybill_no
where  t.dt='{{ execution_date | cst_ds }}'
)


insert overwrite table jms_dm.dm_reverse_marking_performance_detail_dt partition (dt)
select
  t.date_time
, t.arrival_time
, t.waybill_no                --运单号
, t.business_type             --客户标签
, t.is_door                   --是否送货上门,1是0否
, if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0)) as is_performance_flag       --是否派前电联,1是0否
, t.call_start_time           --通话开始时间
, t.call_end_time             --通话结束时间
, t.call_connect_status       --通话状态
, t.call_duration_seconds     --实际通话时长
, t.instore_time              --入库时间
, t.business_name             --投递类型
, t.sign_time                 --签收出库时间
, t.signed_type                                                                            --签收类型
, t.scan_user_code                                                                         --派件员code
, t.scan_user                                                                              --派件员name
, t.scan_site_code                                                                         --扫描网点code
, t.scan_site_name                                                                         --扫描网点name
, t.fran_code                                                                              --加盟商code
, t.fran_name                                                                              --加盟商
, t.agent_code                                                                             --代理区code
, t.agent_name                                                                             --代理区
, t.ordersource_code                                                                       --订单来源coed
, t.ordersource_name                                                                       --订单来源
, t.is_reback                                                                              --是否退件
, t.gp_flg                                  --共配标签,1是0否
, t.pc_flg                                  --PC端标签,1是0否
, case when t.count_num>=2 and sum(if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0)))over(partition by t.receiver_mobile_phone,t.scan_site_code,t.dt) >=1 and t.rn =  1 then 1
       when t.count_num>=2 and sum(if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0)))over(partition by t.receiver_mobile_phone,t.scan_site_code,t.dt) >=1 and t.rn >  1 then 2
       when if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0)) =1 then 1
       else 0 end as source   --1直接来源,2批量件,0其他
, max(if((t.count_num>=2 and t.rn>=2) or sum(if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0)))over(partition by t.receiver_mobile_phone,t.scan_site_code,t.dt) =0 ,null,t.waybill_no)) over(partition by t.receiver_mobile_phone,t.scan_site_code,t.dt ) as up_waybill_no
, case when t.business_type='labelShsm'     and t.is_door=1             then 1 else 0 end as is_door2
, case when t.business_type='labelMustCall' and if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0))=1 then 1 else 0 end as is_performance_flag2
, t.count_num --同一批的单量
, sum(if(t.sign_time>t.call_start_time,1,if(t.is_performance_flag=1,1,0))) over(partition by t.receiver_mobile_phone,t.scan_site_code,t.dt) as sum_performance --同一批的单量是否有派前电联
, t.receiver_mobile_phone --手机号
, t.dt
from dm_reverse_marking_performance_detail_dt_tmp t
distribute by dt,abs(hash(waybill_no))%20

;

